package com.surprise.db.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.surprise.base.bo.CodeCondition;
import com.surprise.base.bo.PageReq;
import com.surprise.db.po.Code;

import lombok.AllArgsConstructor;

/**
 * CodeDAO.java
 * @author nick
 * @since 2015年5月18日
 */
@Repository
public class CodeDAO extends SimpleDAO<Code> {
	public final String columns = "(itemid,userid,shopid,type,code,expiretime,active,addtime)";
	public final String insertStatement = "(?,?,?,?,?,?,?,now())";
	
	@AllArgsConstructor
	public static class CodeSetter implements BatchPreparedStatementSetter {
		private List<Code> items;
		
		@Override
		public void setValues(PreparedStatement ps, int i) throws SQLException {
			Code item = items.get(i);
			int index = 1;
			ps.setLong(index++, item.getItemid());
			ps.setLong(index++, item.getUserid());
			ps.setLong(index++, item.getShopid());
			ps.setInt(index++, item.getType());
			ps.setString(index++, item.getCode());
			ps.setLong(index++, item.getExpiretime());
			ps.setInt(index++, item.getActive());
		}

		@Override
		public int getBatchSize() {
			return items.size();
		}
	}
	
	public void add(List<Code> items) {
		StringBuilder sb = new StringBuilder();
		sb.append("insert into ")
			.append(getTableName())
			.append(columns)
			.append(" values ")
			.append(insertStatement);
		String sql = sb.toString();
		jdbcTemplate.batchUpdate(sql, new CodeSetter(items));
	}
	
	public List<Code> page(CodeCondition condition) {
		StringBuilder sb = new StringBuilder();
		sb.append("select * from ")	
			.append(getTableName());
		long time = System.currentTimeMillis();
		if(condition != null) {
			switch(condition.getActive()) {
			case 0:
				sb.append(" where active = 0 ");
				break;
			case 1:
				sb.append(" where active = 1 ");
				break;
			case 2:
				sb.append(" where expiretime > ").append(time).append(" ");
				break;
			}
		}
		sb.append(" order by updatetime desc ");
		String sql = sb.toString();
		return findBySql(sql);
	}
	
	public Code getItemCode(long uid, long itemid, int type) {		
		String sql = "select * from #table# where userid = ? and itemid = ? and type = ?";
		List<Code> ret = findBySql(sql, new Object[] {
				uid, itemid, type
		});
		if(CollectionUtils.isEmpty(ret)) {
			return null;
		}
		return ret.get(0);
	}
	
	public int countItemCode(long uid, long itemid, int type) {
		String sql = "select count(*) from #table# where userid = ? and itemid = ? and type = ?";
		int num = findSingleBySql(sql, new Object[] {
				uid, itemid, type
		}, Integer.class);
		return num;
	}
	
	public int getItemJoinNum(long itemid, int type) {
		String sql = "select count(*) from #table# where itemid = ? and type = ? ";
		int num = findSingleBySql(sql, new Object[] {
				itemid, type
		}, Integer.class);
		return num;
	}
	
	public List<Code> page(PageReq page, int type, long uid) {
		StringBuilder sb = new StringBuilder();
		sb.append("select * from ").append(getTableName())
				.append(" where type = ? and userid = ? ");
		if (page != null) {
			sb.append(" limit ").append(page.getStart() - 1).append(',')
					.append(page.getNum());
		}
		String sql = sb.toString();
		return jdbcTemplate.query(sql, new Object[] { type, uid },
				new BeanPropertyRowMapper<Code>(Code.class));
	}
	
	public int count(int type, long uid) {
		StringBuilder sb = new StringBuilder();
		sb.append("select count(*) from ")
			.append(getTableName())
			.append(" where type = ? and userid = ? ");
		String sql = sb.toString();
		return jdbcTemplate.queryForInt(sql, type, uid);
	}
	
	public List<Code> findActiveByUserShop(String code, List<Long> shopids) {
		if(StringUtils.isEmpty(code) || CollectionUtils.isEmpty(shopids)) {
			return new ArrayList<Code>();
		}
		StringBuilder sb = new StringBuilder();
		sb.append("select * from ")
			.append(getTableName())
			.append(" where code = ? and active = 0 ");
		if (!shopids.contains(0L)) {
			String idStr = StringUtils.join(shopids, ',');
			sb.append(" and shopid in (").append(idStr).append(") ");
		}
		String sql = sb.toString();
		return jdbcTemplate.query(sql, new Object[] {
				code
		}, new BeanPropertyRowMapper<Code>(Code.class));
	}
	
	public int verify(long opid, long id) {
		StringBuilder sb = new StringBuilder();
		sb.append("update ")
				.append(getTableName())
				.append(" set opid = ?, active = 1 where id = ?");
		String sql = sb.toString();
		return jdbcTemplate.update(sql, opid, id);
	}
}
